﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_FreeZaiko : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_FreeZaiko()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();
            
            this.FreeZaiko.PopupMenuShowing += Staff_PopupMenuShowing;
            this.freeZaikoContext.ItemClicked += staffContext_ItemClicked;
        }

        void staffContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.freeZaikoContext.Hide();            
            switch (e.ClickedItem.Name)
            {                
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Staff_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.freeZaikoContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet freeZaiko = new DataSet();
            DataSet changeStructure = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable freezaikoTable = new DataTable();
            DataTable changestructureTable = new DataTable();
            DataTable resultTable = new DataTable();
            try
            {                
                // Get Free Zaiko from AS400
                masterConnection.Open();
                sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT t1.FRHNBN, t1.FRZASU, t2.ITNAM1, t2.ITHNTP, t2.ITSICD"
                    + " FROM MPFR00 AS t1 LEFT JOIN MPIT00 AS t2 ON t1.FRHNBN=t2.ITHNBN"
                    + " WHERE t1.FRZASU > 0  AND SUBSTRING(t2.ITFILL,16,4)=''0232''"
                    + " AND ((t2.ITHNTP=''P'') OR ((t2.ITHNTP=''M'') AND (t2.ITSICD != '''') AND (t2.ITSICD != ''B02320'')) )')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(freeZaiko);
                freezaikoTable = freeZaiko.Tables[0];
                resultTable = freezaikoTable.Copy();
                adapter.Dispose();
                cmd.Dispose();

                // Get change structure from AS400
                sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT ZUDENO, ZUOHN1, ZUOHN2, ZUOHN3, ZUOHN4, ZUOHN5, ZUOHN6,"
                    + " ZUOHN7, ZUOHN8, ZUNHN1, ZUNHN2, ZUNHN3, ZUNHN4, ZUNHN5, ZUNHN6, ZUNHN7, ZUNHN8"
                    + " FROM MPZU00"
                    + " WHERE ZUSIGI=''3'' OR ZUSIGI=''4''')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(changeStructure);
                changestructureTable = changeStructure.Tables[0];
                adapter.Dispose();
                cmd.Dispose();

                masterConnection.Close();

                // Find change structure and make change for Free Zaiko
                foreach (DataRow freezaiko_row in freezaikoTable.Rows)
                {
                    DataRow[] change_rows = changestructureTable.Select("ZUOHN1='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN2='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN3='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN4='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN5='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN6='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN7='" + freezaiko_row["FRHNBN"].ToString() + "'"
                        + " OR ZUOHN8='" + freezaiko_row["FRHNBN"].ToString() + "'");
                    if (change_rows.Length > 0)
                    {
                        freezaiko_row.BeginEdit();
                        foreach (DataRow change_row in change_rows)
                        {                            
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN1"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN1"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN2"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN2"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN3"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN3"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN4"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN4"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN5"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN5"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN6"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN6"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN7"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN7"].ToString();
                            }
                            if (freezaiko_row["FRHNBN"].ToString() == change_row["ZUOHN8"].ToString())
                            {
                                freezaiko_row["FRHNBN"] = change_row["ZUNHN8"].ToString();
                            }
                        }
                        freezaiko_row.EndEdit();
                    }                    
                }

                resultTable.Columns.Add("Date1", typeof(DateTime));
                resultTable.Columns.Add("Date2", typeof(DateTime));

                // Sum quantity of Free Zaiko with same PartNo
                foreach (DataRow result_row in resultTable.Rows)
                {
                    float quantity = 0;
                    DataRow[] freezaiko_rows = freezaikoTable.Select("FRHNBN='" + result_row["FRHNBN"].ToString() + "'");
                    if (freezaiko_rows.Length > 0)
                    {
                        foreach (DataRow freezaiko_row in freezaiko_rows)
                        {
                            quantity += float.Parse(freezaiko_row["FRZASU"].ToString());
                        }
                    }

                    result_row.BeginEdit();
                    result_row["FRZASU"] = quantity;
                    result_row["Date1"] = DateTime.Now.Date;
                    result_row["Date2"] = DateTime.Now.Date;
                    result_row.EndEdit();
                }

                // Remove Free Zaiko with quantity = 0
                foreach (DataRow result_row in resultTable.Rows)
                {
                    if (float.Parse(result_row["FRZASU"].ToString()) == 0)
                    {
                        result_row.Delete();
                    }
                }
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }
                //MessageBox.Show("Cannot connect to AS400");
                MessageBox.Show(ex.Message);
            }

            if (resultTable.Rows.Count > 0)
            {
                gridFreeZaiko.DataSource = resultTable;
            }
            else
            {
                MessageBox.Show("There is no data for Free Zaiko");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("FRHNBN", typeof(string));
                newTable.Columns.Add("FRZASU", typeof(string));
                newTable.Columns.Add("ITNAM1", typeof(string));
                newTable.Columns.Add("ITHNTP", typeof(string));
                newTable.Columns.Add("ITSICD", typeof(string));
                newTable.Columns.Add("Date1", typeof(DateTime));
                newTable.Columns.Add("Date2", typeof(DateTime));
                gridFreeZaiko.DataSource = newTable;
            }
            FreeZaiko.Columns[0].Caption = "Part No";
            FreeZaiko.Columns[1].Caption = "Quantity";
            FreeZaiko.Columns[2].Caption = "Part Name";
            FreeZaiko.Columns[3].Caption = "Part Type";
            FreeZaiko.Columns[4].Caption = "Supplier ID";
            FreeZaiko.Columns[5].Caption = "Date1";
            FreeZaiko.Columns[6].Caption = "Date2";            
            FreeZaiko.BestFitColumns();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
